# coding: utf-8
import scrapy,time,datetime
from scrapy.http import Request
import pymysql
import urllib.request
import random
import re
from novel.settings import USER_AGENT_LIST
# from urllib.request import urlretrieve
url="https://www.aiqu.la"

class MySpider(scrapy.Spider):
    # 设置name
    name = "book"
    #填写爬取地址
    start_urls=[]
    for i in range(1,24):
        url="https://www.aiqu.la/quanben/%s.html" % i
        start_urls.append(url)


    f= open("log.txt", "r+", encoding="utf-8")
    f.truncate()
    f.close()


    # # 编写爬取方法
    # def parse(self, response):
    #     update_sql_data("update book set is_active=0")
    #     page=response.xpath('//div[@id="PageBar"]/ul').css("a::attr(href)").extract()[-1]
    #     pattern = r'/quanben/(\d+)'
    #     page = re.search(pattern, page)
    #     page = page.group(1)
    #     if page:
    #         for i in range(1,int(page)+1):
    #             url="https://www.aiqu.la/quanben/%s.html" % i
    #             self.log_add("分页开始(%s):%s" % (datetime.datetime.now(),url))
    #             yield Request(url,callback=self.page_details,meta={'fy': i})

    #获取每一列表页的信息
    def parse(self, response):
        pattern = r'/quanben/(\d+)\.html'
        page = re.search(pattern, response.request.url)
        fy = page.group(1)
        self.log_add("分页进行中(%s):%s" % (datetime.datetime.now(),fy))
        book_list = response.xpath('//span[@class="s3"]').css("a::attr(href)").extract()
        for book in book_list:
            pattern = r'/book/(\d+)/(\d+)/(\d+)\.html'
            book_group = re.search(pattern, book)
            book_type = book_group.group(1) if book_group else 0
            third_book_id = book_group.group(2) if book_group else 0
            third_chapter_id = book_group.group(3) if book_group else 0
            book_db = fetchall_dict("select * from book where third_id=%s " % third_book_id)
            if not book_db:
                continue
            if book_db and book_db[0].last_chapterid and book_db[0].cover_img and book_db[0].desc:
                chapter_db = fetchall_dict("select * from chapter where id=%s " % book_db[0].last_chapterid)
                chapter_all_db = fetchall_dict("select count(*) as co from chapter where book_id=%s " % book_db[0].id)
                article_all_db = fetchall_dict("select count(*) as co from article where book_id=%s " % book_db[0].id)
                if chapter_db and chapter_db[0].third_id == int(third_chapter_id) and chapter_all_db[0].co != article_all_db[0].co:
                    continue
            self.log_add("分页(%s)详情页开始(%s):%s" % (fy,datetime.datetime.now(),"https://www.aiqu.la" + book))
            meta={"book_type":book_type,"third_book_id":third_book_id,'fy':fy}
            headers=self.headers()
            yield Request("https://www.aiqu.la" + book,meta=meta,callback=self.book_parse,headers=headers)
    def headers(self):
        rand_use=random.choice(USER_AGENT_LIST)
        headers = {"User-Agent":rand_use}
        return headers

    def book_parse(self, response):
        """
        书的信息和章节信息入库
        :param response:
        :return:
        """
        #分页
        fy = response.meta['fy']
        #书第三方id
        third_book_id = response.meta['third_book_id']
        self.log_add("分页(%s)详情页进行中(%s):%s" % (fy,datetime.datetime.now(),third_book_id))
        #书名称
        book_name=response.xpath('//div[@id="info"]/h1/text()').extract_first()
        if not book_name:
            return
        book_name=pymysql.escape_string(str(book_name))
        #最后更新时间
        update_time = response.xpath("//div[@id='info']//p[3]//text()").re("最后更新：(.*)")
        update_time=int(time.mktime(time.strptime(update_time[0], "%Y-%m-%d %H:%M:%S"))) if update_time else 0
        #作者
        author = response.xpath("//div[@id='info']//p[1]//text()").re("作  者：(.*)")
        author=author[0] if author else ""
        author=pymysql.escape_string(str(author))
        # 书分类名称
        type_name = response.xpath("//div[@class='con_top']//a[2]//text()").extract_first()
        #书分类id
        # third_type_id = response.meta['book_type']
        third_type = response.xpath("//div[@class='con_top']//a[2]").css("a::attr(href)").extract_first()
        third_type = re.search(r'/list(\d+)/', third_type)
        third_type_id = third_type.group(1) if third_type else 0
        #简介
        # desc = response.xpath("//div[@id='intro']//text()").extract_first().strip()
        desc = response.xpath("//div[@id='intro']//text()").extract_first()
        desc=pymysql.escape_string(str(desc.strip())) if desc else ""
        #封面图片
        cover_img = response.css("#fmimg img::attr(src)").extract_first()

        #book_type表更新
        book_type_db=fetchall_dict("select * from book_type where third_id=%s " % third_type_id)
        if not book_type_db:
            book_type_id=create_sql("book_type",{"type_name":type_name,"third_id":third_type_id})
        else:
            book_type_id=book_type_db[0].id

        #book表更新

        book_db=fetchall_dict("select * from book where third_id=%s " % third_book_id)
        if book_db:
            book_id=book_db[0].id
            # book_where="is_active=1"
            # if book_db[0].name !=book_name:
            #     book_where+=",name=%s" % book_name
            # if book_db[0].book_type !=book_type_id:
            #     book_where+=",book_type=%s" % book_type_id
            # if book_db[0].author !=author:
            #     book_where+=",author=%s" % author
            # if book_db[0].desc !=desc:
            #     book_where+=",desc=%s" % desc
            # if book_db[0].update_time !=update_time:
            #     book_where+=",update_time=%s" % update_time
            # update_sql_data("update book set %s where third_id=%s " % (book_where,third_book_id))
        else:
            data_dict={
                "name":book_name,
                "book_type": book_type_id,
                "is_active":1,
                "author": author,
                "desc": desc,
                "add_time":int(time.time()),
                "update_time": update_time,
                "third_id":third_book_id,
                "sequence":third_book_id
            }
            book_id=create_sql("book",data_dict)
        chapter_id=0
        chapter_name=""
        #章节列表
        chapter_list = response.xpath("//div[@id='list']//dl//dd//a").extract()
        for chapte in chapter_list:
            if "title=" not in chapte:
                pattern = r'<a href="/book/(\d+)/(\d+)/(\d+)\.html">(.*)</a>'
                chapter_group = re.search(pattern, chapte)
                if  chapter_group:
                    book_type = chapter_group.group(1)
                    third_book_id = chapter_group.group(2)
                    third_chapter_id = chapter_group.group(3)   #
                    chapter_name=chapter_group.group(4)
                    chapter_name=pymysql.escape_string(str(chapter_name))
                    add_time=int(time.time())
                    chapter_db=fetchall_dict("select * from chapter where third_id=%s " % third_chapter_id)
                    if chapter_db:
                        chapter_id=chapter_db[0].id
                        # chapter_where="is_active=1"
                        # if chapter_db[0].chapter_name !=chapter_name:
                        #     chapter_where+=",chapter_name='%s'" % chapter_name
                        # if chapter_db[0].book_id !=book_id:
                        #     chapter_where+=",book_id=%s" % book_id
                        # update_sql_data("update chapter set %s where third_id=%s " % (chapter_where,third_chapter_id))
                    else:
                        chapter_id=create_sql("chapter",
                        {"book_id":book_id,"chapter_name":chapter_name,"add_time":add_time,"third_id":third_chapter_id,
                         "is_active":1,"sequence":third_chapter_id})
                        # update_sql_data("update chapter set sequence=%s where id=%s " % (chapter_id,chapter_id))
                    chapter_url="https://www.aiqu.la/book/%s/%s/%s.html"% (book_type,third_book_id,third_chapter_id)
                    self.log_add("分页(%s)详情页(%s)章节开始(%s):%s" % (fy,third_book_id,datetime.datetime.now(),third_chapter_id))
                    meta={"fy":fy,"third_chapter_id":third_chapter_id,"third_book_id":third_book_id,"book_id":book_id,"chapter_id":chapter_id}
                    headers=self.headers
                    yield Request(chapter_url,meta=meta, callback=self.chapte_parse,headers=headers)
        book_db=fetchall_dict("select * from book where third_id=%s " % third_book_id)
        if book_db:
            chapter_sql="select * from chapter where book_id=%s and is_active=1 order by id desc limit 1" % book_id
            chapter_db=fetchall_dict(chapter_sql)
            if chapter_db:
                where_sql="last_chapter='%s',last_chapterid=%s" % (chapter_db[0].chapter_name,chapter_db[0].id)
                if where_sql:
                    update_sql_data("update book set %s where third_id=%s " % (where_sql,third_book_id))
                if not book_db[0].img_url:
                    img_url='/home/all_images/images/novel_dj/%s_%s.png' % (book_id,book_type_id)
                    # img_url='E:/lianxi/img/%s_%s.png' % (book_id,book_type_id)
                    result_img=self.imgdon(cover_img,img_url)

                    if result_img:
                        img_url="/images/novel_dj/%s_%s.png" % (book_id,book_type_id)
                        where_sql="cover_img='%s'" % img_url
                        update_sql_data("update book set %s where third_id=%s " % (where_sql,third_book_id))

    def imgdon(self,cover_img,img_url,i=0):
        try:
            rand_use=random.choice(USER_AGENT_LIST)
            headers = ("User-Agent",rand_use)
            opener = urllib.request.build_opener()
            opener.addheaders = [headers]
            urllib.request.install_opener(opener)
            urllib.request.urlretrieve(cover_img, img_url)
        except:
            if i<=7:
                print(cover_img)
                self.imgdon(cover_img,img_url,i+1)
            else:
                now_time = datetime.datetime.now()
                fo = open("foo2.txt", "a", encoding="utf-8")
                err=str(now_time)+"放弃----------%s----------------%s" % (cover_img,img_url)
                fo.write(err)
                fo.write("\n")
                # 关闭打开的文件
                fo.close()
                return 0
        return 1

    def chapte_parse(self, response):
        """
        章节内容入库
        :param response:
        :return:
        """
        chapter_id=response.meta['chapter_id']
        book_id=response.meta['book_id']
        if response.status == 404:
            self.write("chapte_parse_" + str(response.url))
        content = response.css("#content").extract_first()
        content=pymysql.escape_string(str(content))
        fy = response.meta['fy']
        third_book_id = response.meta['third_book_id']
        third_chapter_id = response.meta['third_chapter_id']
        self.log_add("分页(%s)详情页(%s)章节进行中(%s):%s" % (fy,third_book_id,datetime.datetime.now(),third_chapter_id))
        if chapter_id and book_id:
            article_db=fetchall_dict("select * from article where book_id=%s and  chapter_id=%s" % (book_id,chapter_id))
            if article_db:
                pass
                # if article_db[0].content !=content:
                #     update_sql_data("update article set content=%s where id=%s " % (content,article_db[0].id))
            else:
                create_sql("article",{"book_id":book_id,"chapter_id":chapter_id,"content":content})

    def log_add(self,log_str):
        fo = open("log.txt", "a", encoding="utf-8")
        fo.write(log_str)
        fo.write("\n")
        # 关闭打开的文件
        fo.close()



def get_conn():
    conn = pymysql.connect(host='',
                           user='root',
                           port=3306,
                           password='',
                           db='novel',
                           charset='utf8'
                           )
    return conn


class Struct(dict):
    """
    Object-Dict
    """
    def __init__(self, *e, **f):
        if e:
            self.update(e[0])
        if f:
            self.update(f)

    def __getattr__(self, name):
        if name.startswith('__'):
            raise AttributeError
        return self.get(name)

    def __setattr__(self, name, val):
        self[name] = val

    def __delattr__(self, name):
        self.pop(name, None)

    def __hash__(self):
        return id(self)

    def copy(self):
        return Struct(dict.copy(self))


def fetchall_dict(sql, *args):
    args = args or None
    conn = get_conn()
    cursor_tmp = conn.cursor()
    try:
        with cursor_tmp as cursor:
            cursor.execute(sql, args)
            fields = [r[0] for r in cursor.description]
            rows = cursor.fetchall()
        cursor_tmp.close()
        conn.close()
    except  Exception as e:
        now_time = datetime.datetime.now()
        fo = open("foo2.txt", "a", encoding="utf-8")
        err=str(now_time)+"_"+str(e)+"\n"+"sql:"+sql
        fo.write(err)
        fo.write("\n")
        # 关闭打开的文件
        fo.close()
    else:
        return [Struct(zip(fields, row)) for row in rows]


def excute(cur, sql, *args):
    cur.execute(sql, args)
    return cur.rowcount, cur.lastrowid


def create_sql(table_name, kw):
    "Returns lastrowid"
    try:
        conn = get_conn()
        cursor_tmp = conn.cursor()
        tokens = u','.join([u'%s'] * len(kw))
        fields = [u"`{}`".format(k) for k in kw.keys()]
        fields = u','.join(fields)
        ondup_s = ''
        ondup_vals = []
        sql = u"insert into {} ({}) values ({})".format(table_name, fields, tokens)
        values = list(kw.values()) + ondup_vals
        _, lastid = excute(cursor_tmp, sql, *values)
        conn.commit()
        conn.close()
    except  Exception as e:
        now_time = datetime.datetime.now()
        fo = open("foo2.txt", "a", encoding="utf-8")
        err=str(now_time)+"_"+str(e)+"\n"+"sql:"+sql
        fo.write(err)
        fo.write("\n")
        # 关闭打开的文件
        fo.close()
    else:
        return lastid

def update_sql_data(sql):
    try:
        conn = get_conn()
        cursor_tmp = conn.cursor()
        cursor_tmp.execute(sql)
        conn.commit()
        cursor_tmp.close()
        conn.close()
    except  Exception as e:
        now_time = datetime.datetime.now()
        fo = open("foo2.txt", "a", encoding="utf-8")
        err=str(now_time)+"_"+str(e)+"\n"+"sql:"+sql
        fo.write(err)
        fo.write("\n")
        fo.write("\n")
        # 关闭打开的文件
        fo.close()
    else:
        pass













